package makovo.utils;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Dictionary;
import java.util.Hashtable;

import org.apache.commons.io.IOUtils;

public class DataAccess {
	private Dictionary<String, String> mProperties = new Hashtable<>();
	private Connection mConnection;
	private static DataAccess mInstance;

	public static DataAccess getInstance() throws IOException, SQLException {
		if (mInstance == null)
			synchronized (DataAccess.class) {
				if (mInstance == null)
					mInstance = new DataAccess();
			}
		
		return mInstance;
	}

	private DataAccess() throws IOException, SQLException {
		try {
			String[] par;

			String propFile = IOUtils.toString(this.getClass().getResourceAsStream("/makovo/resources/db.properties"));

			String props[] = propFile.split("\n");

			for (String p : props) {
				par = p.replaceFirst("\n", "").split("=");
				mProperties.put(par[0], par[1]);
			}
		} catch (IOException e) {
			throw e;
		}

	}

	private void createConnection() throws Exception {

		try {
			Class.forName(mProperties.get("db_driver"));

			mConnection = DriverManager.getConnection(mProperties.get("db_connection"), mProperties.get("db_user"),
					mProperties.get("db_password"));
		} catch (SQLException | ClassNotFoundException e) {
			throw e;
		}

	}

	public CallableStatement getCommand(String procedure, ArrayList<String[]> pars) throws Exception {
		CallableStatement command = null;

		try {
			if (mConnection == null || mConnection.isClosed())
				createConnection();

			command = mConnection.prepareCall(String.format("{call %s}", procedure));

			if (pars != null) {
				int i = 1;
				for (String[] par : pars)
					command.setString(i++, par[1]);

			}
		} catch (Exception e) {
			throw e;
		}

		return command;
	}

	public void exec(String procedure, ArrayList<String[]> pars) throws Exception {
		try {
			getCommand(procedure, pars).executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}
	}

	@SuppressWarnings("unchecked")
	public <T> T execValue(String procedure, ArrayList<String[]> pars) throws Exception {
		T ret = null;
		try {
			ResultSet result = getCommand(procedure, pars).executeQuery();

			if (result.first())
				ret = (T) result.getObject(1);

			result.close();

		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}

		return ret;
	}

	public ResultSet execResult(String procedure, ArrayList<String[]> pars) throws Exception {
		try {
			return getCommand(procedure, pars).executeQuery();
		} catch (Exception e) {
			throw e;
		}
	}

	public void close() throws SQLException {
		if (mConnection != null)
			if (!mConnection.isClosed())
				mConnection.close();
	}
}
